Re: [GENERAL] How to do this in SQL?

This question's topic is more appropriate to the SQL list than to the
GENERAL list, so I am redirecting it there.

At 16:45 +0200 on 30/03/1999, Chris Bitmead wrote:

>
> Let's say I had a table...
> CREATE TABLE book (
>    author oid,
>    name text );
> and...
> CREATE TABLE author (
>    name text );
>
> and I wanted to create a book pointing to author with name 'Tolstoy'. I
> want to do something like...
>
> INSERT INTO book(name,author) values('War and Peace',
>       (SELECT oid FROM author WHERE name = 'Tolstoy'));
>
> but this doesn't work. What is the correct syntax?

This is not the correct syntax. The proper one is:

INSERT INTO BOOK (name, author)
SELECT 'War and Peace', oid
FROM author
WHERE name = 'Tolstoy';

This syntax appears in the documentation for the INSERT command, but most
people disregard it, because they thing the SELECT statement must only
select fields or expressions that involve fields. The truth is that the
SELECT statement can select contstant-valued expressions. Perhaps an
example in this spirit should be added to the documentation.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma